import random

import pymysql
from flask import Flask, redirect

app = Flask(__name__)


def get_connection():
    return pymysql.connect(
        host='47.104.31.138', port=3306, user='guest',
        password='Guest.618', database='data_viz', charset='utf8mb4'
    )


@app.route('/')
def show_index():
    # 请求首页，重定向到/static/html/index.html
    return redirect('/static/html/index.html')


@app.route('/api/stats')
def get_stats_data():
    # Todo: 修改为通过SQL查询到的真实数据
    return {'gmv': 12.85, 'arpu': 3215.65, 'backRate': 12.3}


@app.route('/api/channel')
def get_channel_data():
    conn = get_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute('select chanelID as name, count(chanelID) as value '
                           'from tb_order group by chanelID '
                           'order by value desc limit 5')
            return {'data': cursor.fetchall()}
    finally:
        conn.close()


# 路径参数
@app.route('/api/gmv/<int:year>')
def get_gmv_by_month(year):
    # 通过URL参数（查询参数，URL最后的?后的参数）
    # year = int(request.args.get('year'))
    conn = get_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute('select '
                           '    month(orderTime) as month, '
                           '    sum(orderAmount) as total '
                           'from tb_order '
                           'where year(orderTime)=%s '
                           'group by month '
                           'order by month asc', year)
            all_rows = cursor.fetchall()
            x_data = [f'{row["month"]}月' for row in all_rows]
            y_data = [round(float(row['total']) / 10000, 2) for row in all_rows]
    finally:
        conn.close()
    return {'xData': x_data, 'yData': y_data}


@app.route('/api/sales')
def get_sales_data():
    group_a = [random.randint(5, 50) for _ in range(6)]
    group_b = [random.randint(5, 50) for _ in range(6)]
    return {
        'legend': ['销售A组', '销售B组'],
        'xData': ['衬衫', '羊毛衫', '雪纺衫', '裤子', '高跟鞋', '袜子'],
        'yData': [group_a, group_b]
    }


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8000, debug=True)
